﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Lesson2Practice
{
	public partial class Form1 : Form
	{
		private SqlDataAdapter _customersDataAdapter;
		private SqlDataAdapter _ordersDataAdapter;

		public Form1()
		{
			InitializeComponent();
		}

		private void Form1_Load(object sender, EventArgs e)
		{
			var connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
			InitializeDataAdapters();
			PopulateDataSet();
		}

		private void InitializeDataAdapters()
		{
			var connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

			_customersDataAdapter = new SqlDataAdapter("SELECT * FROM Customers", connectionString);
			var bldCustomers = new SqlCommandBuilder(_customersDataAdapter);

			var connection = new SqlConnection(connectionString);
			var cmdSelectOrders = CreateOrdersSelectCommand(connection);
			var cmdInsertOrders = CreateOrdersInsertCommand(connection);
			var cmdUpdateOrders = connection.CreateCommand();
			var cmdDeleteOrders = connection.CreateCommand();
			

		}

		#region OrdersCommands

		private SqlCommand CreateOrdersSelectCommand(SqlConnection connection)
		{
			var ordersSelectCommand = connection.CreateCommand();
			ordersSelectCommand.CommandText = "SELECT * FROM Orders";
			return ordersSelectCommand;
		}

		private SqlCommand CreateOrdersInsertCommand(SqlConnection connection)
		{
			var ordersInsertCommand = connection.CreateCommand();
			ordersInsertCommand.CommandText =
				"INSERT Orders(CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry) " +
				"OUTUPUT inserted.* " +
				"VALUES(@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry)";
			ordersInsertCommand.Parameters.Add("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID");
			ordersInsertCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
			ordersInsertCommand.Parameters.Add("@OrderDate", SqlDbType.DateTime, 0, "OrderDate");
			ordersInsertCommand.Parameters.Add("@RequiredDate", SqlDbType.DateTime, 0, "RequiredDate");
			ordersInsertCommand.Parameters.Add("@ShippedDate", SqlDbType.DateTime, 0, "ShippedDate");
			ordersInsertCommand.Parameters.Add("@ShipVia", SqlDbType.Int, 0, "ShipVia");
			ordersInsertCommand.Parameters.Add("@Freight", SqlDbType.Money, 0, "Freight");
			ordersInsertCommand.Parameters.Add("@ShipName", SqlDbType.NVarChar, 40, "ShipName");
			ordersInsertCommand.Parameters.Add("@ShipAddress", SqlDbType.NVarChar, 60, "ShipAddress");
			ordersInsertCommand.Parameters.Add("@ShipCity", SqlDbType.NVarChar, 15, "ShipCity");
			ordersInsertCommand.Parameters.Add("@ShipRegion", SqlDbType.NVarChar, 15, "ShipRegion");
			ordersInsertCommand.Parameters.Add("@ShipPostalCode", SqlDbType.NVarChar, 10, "ShipPostalCode");
			ordersInsertCommand.Parameters.Add("@ShipCountry", SqlDbType.NVarChar, 15, "ShipCountry");

			return ordersInsertCommand;
		}
		
		private SqlCommand CreateOrdersUpdateCommand(SqlConnection connection)
		{
			var ordersUpdateCommand = connection.CreateCommand();

			ordersUpdateCommand.CommandText = "UPDATE ORDERS SET " +
			                                  "CustomerID = @CustomerID" +
			                                  "EmployeeID = @EmployeeID" +
			                                  "OrderDate = @OrderDate" +
			                                  "RequiredDate = @RequiredDate" +
			                                  "ShippedDate = @ShippedDate" +
			                                  "ShipVia = @ShipVia" +
			                                  "Freight = @Freight" +
			                                  "ShipName = @ShipName" +
			                                  "ShipAddress = @ShipAddress" +
			                                  "ShipCity = @ShipCity" +
			                                  "ShipRegion = @ShipRegion" +
			                                  "ShipPostalCode = @ShipPostalCode" +
			                                  "ShipCountry = @ShipCountry" +
			                                  "WHERE OrderID = @OrderID";

			ordersUpdateCommand.Parameters.Add("@OrderID", SqlDbType.Int, 0, "OrderID").SourceVersion = DataRowVersion.Original;
			ordersUpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID");
			ordersUpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
			ordersUpdateCommand.Parameters.Add("@OrderDate", SqlDbType.DateTime, 0, "OrderDate");
			ordersUpdateCommand.Parameters.Add("@RequiredDate", SqlDbType.DateTime, 0, "RequiredDate");
			ordersUpdateCommand.Parameters.Add("@ShippedDate", SqlDbType.DateTime, 0, "ShippedDate");
			ordersUpdateCommand.Parameters.Add("@ShipVia", SqlDbType.Int, 0, "ShipVia");
			ordersUpdateCommand.Parameters.Add("@Freight", SqlDbType.Money, 0, "Freight");
			ordersUpdateCommand.Parameters.Add("@ShipName", SqlDbType.NVarChar, 40, "ShipName");
			ordersUpdateCommand.Parameters.Add("@ShipAddress", SqlDbType.NVarChar, 60, "ShipAddress");
			ordersUpdateCommand.Parameters.Add("@ShipCity", SqlDbType.NVarChar, 15, "ShipCity");
			ordersUpdateCommand.Parameters.Add("@ShipRegion", SqlDbType.NVarChar, 15, "ShipRegion");
			ordersUpdateCommand.Parameters.Add("@ShipPostalCode", SqlDbType.NVarChar, 10, "ShipPostalCode");
			ordersUpdateCommand.Parameters.Add("@ShipCountry", SqlDbType.NVarChar, 15, "ShipCountry");


		}

		#endregion OrdersCommands

		private void PopulateDataSet()
		{
			
		}
	}
}
